package br.com.fiap.mercadinho.dao;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import br.com.fiap.mercadinho.bean.Customer;
import br.com.fiap.mercadinho.common.DAOException;
import br.com.fiap.mercadinho.common.GenericDAOException;

public class CustomerDAO extends GenericDAO {

	private final static String INSERT_CUSTOMER = " INSERT INTO CUSTOMER "
			+ "(CUSTOMER_ID,NAME,CPF_NUMBER,BIRTH_DATE)" + "  VALUES (0,?,?,?)";

	private final static String FIND_ALL_CUSTOMER = " SELECT CUSTOMER_ID,"
			+ "        NAME," + "        CPF_NUMBER,"
			+ "        date_format(birth_date,'%d/%m/%Y') as BIRTH_DATE "
			+ "    FROM CUSTOMER ";

	private final static String FIND_CUSTOMER_BY_ID = " SELECT CUSTOMER_ID,"
			+ "      NAME," + "      CPF_NUMBER,"
			+ "   date_format(birth_date,'%d/%m/%Y') as  BIRTH_DATE "
			+ "    FROM CUSTOMER " + "   WHERE CUSTOMER_ID = ? ";
	

	private final static String FIND_CUSTOMER_BY_CPF = " SELECT CUSTOMER_ID,"
			+ "      NAME," + "      CPF_NUMBER,"
			+ "   date_format(birth_date,'%d/%m/%Y') as  BIRTH_DATE "
			+ "    FROM CUSTOMER " + "   WHERE CPF_NUMBER = ? ";

	private final static String UPDATE_CUSTOMER = " UPDATE CUSTOMER SET NAME = ?, "
			+ "    CPF_NUMBER = ?,"
			+ "    BIRTH_DATE = ? "
			+ "  WHERE CUSTOMER_ID = ? ";

	public CustomerDAO() {

	}

	public void saveCustomer(Customer customer) throws DAOException {
		try {
			openConnection();
			PreparedStatement pst = conn.prepareStatement(INSERT_CUSTOMER);
			pst.setString(1, customer.getName());
			pst.setString(2, customer.getCpfNumber());
			pst.setDate(3, new Date(customer.getBirthDate().getTime()));

			pst.executeUpdate();
		} catch (GenericDAOException | SQLException e) {
			throw new DAOException("Error in insert customer method: "
					+ e.getMessage());
		}finally{
			try {
				closeConnction();
			} catch (GenericDAOException e) {
				throw new DAOException("Error in close connection method: "
						+ e.getMessage());
			}
		}
	}

	public List<Customer> findAllCustomer() throws DAOException {
		try {
			openConnection();
			PreparedStatement pst = conn.prepareStatement(FIND_ALL_CUSTOMER);
			ResultSet rs = pst.executeQuery();
			List<Customer> listCustomers = new ArrayList<>();

			while (rs.next()) {
				listCustomers.add(buildCustomer(rs));
			}
			return listCustomers;
		} catch (GenericDAOException | SQLException e) {
			throw new DAOException("Error in insert customer method: "
					+ e.getMessage());
		}finally{
			try {
				closeConnction();
			} catch (GenericDAOException e) {
				throw new DAOException("Error in close connection method: "
						+ e.getMessage());
			}
		}
	}

	public Customer findCustomerById(Integer id) throws DAOException {
		try {
			openConnection();
			PreparedStatement pst = conn.prepareStatement(FIND_CUSTOMER_BY_ID);
			pst.setInt(1, id);
			ResultSet rs = pst.executeQuery();
			Customer customer = null;

			while (rs.next()) {
				customer = buildCustomer(rs);
			}
			return customer;
		} catch (GenericDAOException | SQLException e) {
			throw new DAOException("Error in insert customer method: "
					+ e.getMessage());
		}finally{
			try {
				closeConnction();
			} catch (GenericDAOException e) {
				throw new DAOException("Error in close connection method: "
						+ e.getMessage());
			}
		}
	}
	
	public Customer findCustomerByCPF(final String cpf) throws DAOException {
		try {
			openConnection();
			PreparedStatement pst = conn.prepareStatement(FIND_CUSTOMER_BY_CPF);
			pst.setString(1, cpf);
			ResultSet rs = pst.executeQuery();
			Customer customer = null;

			while (rs.next()) {
				customer = buildCustomer(rs);
			}
			return customer;
		} catch (GenericDAOException | SQLException e) {
			throw new DAOException("Error in insert customer method: "
					+ e.getMessage());
		}finally{
			try {
				closeConnction();
			} catch (GenericDAOException e) {
				throw new DAOException("Error in close connection method: "
						+ e.getMessage());
			}
		}
	}
	

	public void updateCustomer(Customer customer) throws DAOException {

		try {
			openConnection();
			PreparedStatement pst = conn.prepareStatement(UPDATE_CUSTOMER);
			pst.setString(1, customer.getName());
			pst.setString(2, customer.getCpfNumber());
			pst.setDate(3, new Date(customer.getBirthDate().getTime()));
			pst.setInt(4, customer.getId());
			pst.executeUpdate();
		} catch (GenericDAOException | SQLException e) {
			throw new DAOException("Error in insert customer method: "
					+ e.getMessage());
		}finally{
			try {
				closeConnction();
			} catch (GenericDAOException e) {
				throw new DAOException("Error in close connection method: "
						+ e.getMessage());
			}
		}

	}

	private Customer buildCustomer(ResultSet rs) throws SQLException {
		Customer cust;
		cust = new Customer();
		cust.setId(rs.getInt("CUSTOMER_ID"));
		cust.setName(rs.getString("NAME"));
		cust.setCpfNumber(rs.getString("CPF_NUMBER"));
		cust.setBirthDate(rs.getString("BIRTH_DATE"));
		return cust;
	}

}
